import os
import sqlite3

CONF_DIR = os.getenv('HOME')+'/.uit'
CONF_FILE = CONF_DIR + '/conf.sqlite3.db'
OUT = os.getenv('HOME')+'/uit-out'

ddl="""
create table tags (
  path text,
  tag text
);

create table keywords (
  path text,
  keywords text
);
"""

def connect():
	con = sqlite3.connect(CONF_FILE)
	con.isolation_level = None
	return con

def _exec_sql(sql, params=[]):
	con = connect()
	con.execute(sql, params)
	con.close()

def _select(sql, params=[]):
	con = connect()
	cur = con.cursor()
	cur.execute(sql, params)
	result = cur.fetchall()
	con.close()
	return result

def _init():
	if os.path.exists(CONF_FILE):
		return
	if not os.path.exists(CONF_DIR):
		os.mkdir(CONF_DIR)
	for sql in ddl.split(';'):
		_exec_sql(sql)

def add_tag(path, tag):
	_init()
	_exec_sql('insert into tags (path, tag) values (?, ?)', (path,tag))

def path_tags(path):
	_init()
	rs = _select('select tag from tags where path = :path', {'path':path} )
	return [t[0] for t in rs]
	
def paths_tags():
	_init()
	rs = _select('select path, tag from tags')
	return [(t[0],t[1]) for t in rs]
	
def set_keywords(path, keywords):
	_init()
	_exec_sql('delete from keywords where path = :path', {'path':path})
	if keywords is None:
		return
	keywords = keywords.strip()
	if len(keywords) == 0:
		return
	_exec_sql('insert into keywords (path, keywords) values(?, ?)', (path, keywords))

def path_keywords(path):
	_init()
	rs = _select('select keywords from keywords where path = :path', {'path':path} )
	if len(rs)==1:
		return rs[0][0]
	return None
	
def total_stat():
	_init()
	rs = _select('select distinct path from ('+ \
		'select path from tags  union  select path from keywords'+ \
		') order by path')
	return [t[0] for t in rs]

